use Student
go

--1.查询学生的姓名，年龄，笔试成绩和机试成绩
select * from stuinfo
select * from stuexam

select stuName as 学生姓名,stuAge as 学生年龄,wittenExam as 笔试成绩,labExam as 机试成绩 from stuinfo
join stuexam on stuinfo.stuNO=stuexam.stuNo

--2.查询笔试和机试成绩都在60分以上的学生的学号，姓名，笔试成绩和机试成绩
select stuinfo.stuNo,stuName,wittenExam,labExam from stuinfo
join stuexam on stuinfo.stuNo=stuexam.stuNo where wittenExam > 60 and labExam > 60

--3.查询所有学生的学号，姓名，笔试成绩，机试成绩，没有参加考试的学生的成绩以NULL值填充
select stuinfo.stuNo,stuName,wittenExam,labExam from stuinfo
left join stuexam on stuinfo.stuNo=stuexam.stuNo

--4.查询年龄在20以上（包括20）的学生的姓名，年龄，笔试成绩和机试成绩，并按笔试成绩降序排列
select stuName,stuAge,wittenExam,labExam from stuinfo
join stuexam on stuinfo.stuNo=stuexam.stuNo where stuAge>=20 order by stuAge desc

--5.查询男女生的机试平均分
select * from stuinfo
select * from stuexam

select stuSex as 性别,avg(labExam) as 平均分 from stuinfo
join stuexam on stuinfo.stuNo=stuexam.stuNo
group by stuSex

--6.查询男女生的笔试总分
select * from stuinfo
select * from stuexam

select stuSex as 性别,sum(wittenExam) as 总分 from stuinfo
join stuexam on stuinfo.stuNo=stuexam.stuNo
group by stuSex

use Iteminfo
go
--7.查询所有的订单的订单的编号，订单日期，订购产品的类别和订购的产品名称，订购数量和订购单价
select * from orderItem
select * from orders

select orders.orderId as 订单编号,orderDate as 订单日期,itemType as 产品类别,itemName as 产品名称,theNumber as 订购数量,theMoney as 订购单价 from orderItem 
join orders on orders.orderId=orderItem.orderId

--8.查询订购数量大于50的订单的编号，订单日期，订购产品的类别和订购的产品名称
select orders.orderId as 订单编号,orderDate as 订单日期,itemType as 产品类别,itemName as 产品名称 from orderItem
join orders on orders.orderId=orderItem.orderId
where theNumber > 50

--9.查询所有的订单的订单的编号，订单日期，订购产品的类别和订购的产品名称，订购数量和订购单价以及订购总价
select orders.orderId as 订单编号,orderDate as 订单日期,itemType as 产品类别,itemName as 产品名称,theNumber as 订购数量,theMoney as 订购单价,thenumber*themoney as 订购总价 from orderItem
join orders on orders.orderId=orderItem.orderId

--10.查询单价大于等于5并且数量大于等于50的订单的订单的编号，订单日期，订购产品的类别和订购的产品名称，订购数量和订购单价以及订购总价
select orders.orderId as 订单编号,orderDate as 订单日期,itemType as 产品类别,itemName as 产品名称,theNumber as 订购数量,theMoney as 订购单价,thenumber*themoney as 订购总价 from orderItem
join orders on orders.orderId=orderItem.orderId
where theMoney >= 5 and theNumber >= 50

--11.查询每个订单分别订购了几个产品，例如： 
			--编号   订购产品数
            --	1        3
            --	2        4
select * from orderItem
select * from orders

select orders.orderId as 产品编号,count(theNumber) as 订购产品数 from orderItem
join orders on orders.orderId=orderItem.orderId
group by orders.orderId

--12.查询每个订单里的每个类别的产品分别订购了几次和总数量，例如：

 --订单编号       产品类别     订购次数     总数量

  --  1           文具            	2           82
  --  1           体育用品        	1           1
  --  2           文具            	2           56
  --  2           体育用品        	1            2
  --  2           日常用品        	1           20

 select orders.orderId as 订单编号,itemType as 产品类别,count(itemType) as 订购次数,sum(theNumber) as 总数量 from orderItem
 join orders on orders.orderId=orderItem.orderId
 group by orders.orderId,itemType

 use bbs
 --13.查询出每个版块的版主编号bbsSection sUid，版主姓名bbsUsers uName和版块名称bbsSection sName 
 select sUid as 版主编号,uName as 版主姓名,sName as 板块名称 from bbsSection
 join bbsUsers on bbsSection.sID=bbsUsers.UID

 --14.查询出主贴的发帖时间在2008-9-15以后的主贴的发帖人编号，发帖人姓名，帖子的标题，帖子的内容和发帖时间
 select tUID as 发帖人编号,uName as 发帖人姓名,tTitle as 帖子的标题,tMsg as 帖子的内容,tTime as 发帖时间 from bbsTopic
 join bbsUsers on bbsTopic.tID=bbsUsers.UID
 where tTime > '2008-9-15'

 --15.查询出年龄在20以下的版主的编号，版主的名称和版块的名称
 select sUid as 版主编号,uName as 版主姓名,sName as 板块名称 from bbsSection
 join bbsUsers on bbsSection.sID=bbsUsers.UID
 where uAge < 20

 --16.查询出回复数量最多的主贴的发帖人编号，发帖人姓名，主贴标题，主贴内容和回复数量
 select tUID,uName,tTitle,tMsg,tCount from bbsTopic
 join bbsUsers on bbsTopic.tID=bbsUsers.UID
 where tCount=(select max(tCount) from bbsTopic)

 --17.在主贴表中查询每个版块中每个用户的发帖总数
 select tUID as 用户,count(tUID) 发帖总数 from bbsTopic
 join bbsSection on bbsTopic.tSID=bbsSection.sID
 group by tUID

 use Student
 --18. 查询 李逍遥(编号id为9) 所在的班级名称（连接查询 2表）
 select StudentId,StudenName,ClassName from Student
 join Class on student.ClassId=Class.ClassId
 where StudentId=9

 --19.查询 李逍遥(学生编号id为9) 学习的课程有哪几门，需要姓名、课程名称、课程学分（连接查询）
 select StudenName,CourseName,CourseCredit from Student
 join Score on Student.StudentId=Score.StudentId
 join Course on Score.CourseId=Course.CourseId
 where Student.StudentId=9

 --20.查询 李逍遥(学生编号id为9) 学习的课程考试得分，需要姓名、课程名称、课程学分、得分（连接查询）
 select StudenName,CourseName,CourseCredit,Score from Student
 join Score on Student.StudentId=Score.StudentId
 join Course on Score.CourseId=Course.CourseId
 where Student.StudentId=9

 --21.使用子查询查询 软件一班的每个学生 的平均分（聚合查询 + 子查询 + 分组）
select * from Student
select * from Score
select * from Class

select StudentId as 学号,avg(Score) as 平均成绩 from Score
where StudentId in (select StudentId from Student where ClassId=1)
group by StudentId

--22.使用连接查询 软件二班的每个学生 的平均分（聚合查询 + 连接查询 + 分组
select Student.StudentId as 学号,avg(Score) as 平均分 from Student
join Score on Student.StudentId=Score.StudentId
where ClassId=2
group by Student.StudentId

--23.按照班级查询所有课程的平均分，并且按照平均分高低进行排序。（聚合查询 + 连接查询 + 分组）
select ClassName,CourseName,avg(Score) as 平均分 from Class
join ClassCourse on Class.ClassId=ClassCourse.ClassId
join Course on ClassCourse.CourseId=Course.CourseId
join Score on Course.CourseId=Score.CourseId
group by ClassName,CourseName
order by avg(Score) desc